﻿Imports System.Data.SqlClient

Public Class Maestros_ADO
    ReadOnly oConnections As New ConexionADO()
    ReadOnly oCnx As New SqlConnection(oConnections.GetCnx)
    ReadOnly oCnxCtb As New SqlConnection(ConexionADO.GetCnxContabilidad)
    ReadOnly cmd As New SqlCommand()

    Public Function pr_GetAerolinea() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodEmpresa, NomEmpresa, CodAnalisis FROM dbo.M_EmpresaTransporte"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetAerolinea) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetAeropuerto(ByVal sOrigen As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT nroCorrelativo, nomAeropuerto FROM dbo.M_Aeropuerto WHERE (codOrigen = '{0}')", sOrigen)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetAeropuerto) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetAnalisis(ByVal sFiltro As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT Codanl, Desanl, Codanl+' '+Desanl AS Descripcion1 FROM dbo.Analisis WHERE (LEN(codanl)=6) {0}", sFiltro)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetAnalisis) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetComunidades() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codComunidad, desComunidad FROM dbo.M_Comunidad WHERE (codComunidad<>'0000000')"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetComunidades) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetContratante() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codContrataViaje, desContrataViaje, CodAnalisis FROM dbo.M_Contratante"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetContratante) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetCuentas(ByVal sTipo As String) As DataTable
        Dim sql As String = ""
        ' si sTipo='1' busca las cuentas que estan en la tabla M_Costos_Reserva
        If sTipo = "1" Then
            sql = "SELECT Codcta, Descta, Codcta+' '+Descta AS Descripcion1 FROM dbo.plan_cuentas WHERE (codcta IN (SELECT codcta FROM dbo.M_Costos_Reserva) Or LEFT(codcta,1) IN (select codcta from dbo.M_Costos_Reserva WHERE len(codcta)=1) Or LEFT(codcta,2) IN (select codcta from dbo.M_Costos_Reserva WHERE len(codcta)=2) Or LEFT(codcta,3) IN (select codcta from dbo.M_Costos_Reserva WHERE len(codcta)=3) Or LEFT(codcta,5) IN (select codcta from dbo.M_Costos_Reserva WHERE len(codcta)=5)) AND len(codcta)=7"
        Else
            ' sino trae todas las cuentas a 7 digitos
            sql = "SELECT Codcta, Descta, Codcta+' '+Descta AS Descripcion1 FROM dbo.Plan_cuentas WHERE (LEN(codcta)=7)"
        End If
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetCuentas) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetDate() As DateTime
        Dim dFechaSistema As DateTime = DateTime.Now
        Const sql As String = "SELECT GETDATE() AS FchServidor"
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(sql, cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("Serv")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            dFechaSistema = drCurrent("FchServidor")
                        Next
                    End Using
                End Using
                cn.Close()
                Return dFechaSistema
            Catch ex As Exception
                cn.Close()
                MsgBox("Maestros_ADO (pr_GetDate) --> " & ex.Message, MsgBoxStyle.Critical)
                Return dFechaSistema
            End Try
        End Using
    End Function

    Public Function pr_GetDiario() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnxCtb
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codasn,desasn FROM dbo.Diarios"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetDiario) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
        Return dt
    End Function

    Public Function pr_GetEstadoReserva() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codEstadoReserva, desEstadoReserva FROM dbo.m_estado_reserva"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetEstadoReserva) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetEstadoVuelo() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codEstadoVuelo, desEstadoVuelo FROM dbo.m_estado_vuelo"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetEstadoVuelo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetLugarResidencia() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodLugar, NomLugar FROM dbo.M_Lugar WHERE TipLugar='R'"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetLugarResidencia) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetLugarDestinoVuelo() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodLugar, NomLugar FROM dbo.M_Lugar WHERE TipLugar='L'"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetLugarDestinoVuelo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetLugarOrigenVuelo(ByVal sUsuario As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT CodLugar, NomLugar FROM dbo.M_Lugar WHERE TipLugar='L' AND CodLugar IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='{0}'))", sUsuario.Trim)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetLugarOrigenVuelo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetMonedas() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codMoneda, nomMoneda FROM dbo.M_Moneda"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetMonedas) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetMotivoViaje() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codMotivo, desMotivo FROM dbo.M_MotivoViaje"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetMotivoViaje) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetPeriodo() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnxCtb
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT anomes,desmes FROM dbo.Meses"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetPeriodo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
        Return dt
    End Function

    Public Function pr_GetPerfiles() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT desnive,codnive FROM dbo.Niveles"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetPerfiles) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetResponsableVuelo(ByVal sOrigen As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT nroCorrelativo, nomResponsable, telResponsable FROM dbo.M_ResponsableVuelo WHERE (codOrigen = '{0}')", sOrigen)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetResponsableVuelo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetRutaVuelo(ByVal sUuario As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT ru.CodOrigen+ru.CodDestino AS codigo,lo.NomLugar+'/'+ld.NomLugar AS descripcion FROM dbo.m_Rutas ru INNER JOIN dbo.m_Lugar lo on ru.CodOrigen=lo.CodLugar INNER JOIN dbo.m_Lugar ld on ru.CodDestino=ld.CodLugar WHERE (ru.flginactivo = 0) AND (ru.CodOrigen IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='{0}')))", sUuario)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetRutaVuelo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetTipoCambio(ByVal sFecha As String) As Decimal
        Dim nmonven As Decimal = 0
        Dim sql As String = String.Format("select moncom,monven from dbo.tipo_cambio where fchmov='{0}'", sFecha)
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(sql, cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("tcam")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            nmonven = drCurrent("monven")
                        Next
                    End Using
                End Using
                cn.Close()
                Return nmonven
            Catch ex As Exception
                cn.Close()
                MsgBox("Maestros_ADO (pr_GetTipoCambio) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_GetTipoDocumento() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codTipoDocumento, desTipoDocumento FROM dbo.M_Tipo_Documento"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetTipoDocumento) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetTipoPasajero() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodTipoPasajero, NomTipoPasajero FROM dbo.M_TipoPasajero WHERE flgInactivo=0"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetTipoPasajero) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetTipoValija() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT TipoValija, desTipoValija FROM dbo.M_Tipo_Valija"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetTipoValija) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetTipoVuelo(ByVal sCodEmp As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT CodTipo, NomTipo FROM dbo.M_TipoVuelo WHERE (CodEmpresa='{0}')", sCodEmp)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch myException As Exception
            MsgBox("Maestros_ADO (pr_GetTipoVuelo) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_CopyPerfiles(ByVal p_codnive As String, ByVal p_codnivecopy As String) As Boolean
        Dim cn As New SqlConnection(oConnections.GetCnx)
        Using cn
            cn.Open()
            Using oTran As SqlTransaction = cn.BeginTransaction(IsolationLevel.Serializable)
                Dim sSql As String
                sSql = "INSERT INTO dbo.Niveles_Menu (codnive,codcla,codpar,opccre,opcmod,opceli,opccon,opcesp) " & _
                      " SELECT @codnive,codcla,codpar,opccre,opcmod,opceli,opccon,opcesp FROM dbo.Niveles_Menu WHERE codnive=@codnivecopy "
                Dim cmdSelect As New SqlCommand(sSql, cn) With {.Transaction = oTran}
                cmdSelect.Parameters.AddWithValue("@codnive", p_codnive)
                cmdSelect.Parameters.AddWithValue("@codnivecopy", p_codnivecopy)
                Try
                    cmdSelect.ExecuteNonQuery()
                    oTran.Commit()
                    Return True
                Catch myException As Exception
                    MsgBox("Perfil_MenuADO (pr_CopyPerfiles) --> " & myException.Message, MsgBoxStyle.Critical)
                    oTran.Rollback()
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_SetPerfiles(ByVal sdesnive As String, ByVal scodnivecopy As String) As Boolean
        Dim cn As New SqlConnection(oConnections.GetCnx)
        Using cn
            cn.Open()
            Using oTran As SqlTransaction = cn.BeginTransaction(IsolationLevel.Serializable)
                Const sInstruccion1 As String = "SELECT cast(max(codnive) as integer) as corre FROM dbo.Niveles "
                Dim cmdSelect As New SqlCommand(sInstruccion1, cn) With {.Transaction = oTran}
                Dim t As Integer = CInt(cmdSelect.ExecuteScalar())
                Dim scodnive As String = CType(t + 1, String)
                scodnive = Right("00" & scodnive.Trim, 2)
                Const sInstruccion As String = "INSERT INTO dbo.Niveles(codnive,desnive) VALUES (@codnive,@desnive) "
                Dim cmd As New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                cmd.Parameters.AddWithValue("@codnive", scodnive)
                cmd.Parameters.AddWithValue("@desnive", sdesnive)
                Try
                    cmd.ExecuteNonQuery()
                    If scodnivecopy <> "" Then
                        If pr_CopyPerfiles(scodnive, scodnivecopy) Then
                        End If
                    End If
                    oTran.Commit()
                    Return True
                Catch myException As Exception
                    MsgBox("Maestros_ADO (pr_SetPerfiles) --> " & myException.Message, MsgBoxStyle.Critical)
                    oTran.Rollback()
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_GetMotivoExceso() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodTipoMotivoExceso, DesTipoMotivoExceso, FacTipoMotivoExceso FROM dbo.M_Motivo_Exceso"
            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetMotivoExceso) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetRegimen() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodRegimen,DesRegimen, FacRegimen FROM dbo.M_Regimen "
            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Maestros_ADO (pr_GetRegimen) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GetFactorRegimen(ByVal sCodRegimen As String) As Decimal
        Dim t_FacRegimen As Decimal = 0
        Dim sql As String = String.Format("select facregimen from dbo.M_Regimen where CodRegimen='{0}'", sCodRegimen)
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(sql, cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("treg")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            t_FacRegimen = drCurrent("facregimen")
                        Next
                    End Using
                End Using
                cn.Close()
                Return t_FacRegimen
            Catch ex As Exception
                cn.Close()
                MsgBox("Maestros_ADO (pr_GetRegimen) --> " & ex.Message, MsgBoxStyle.Critical)
                Return 0
            End Try
        End Using
    End Function


End Class
